105-2008: Using the SAS® DATA Step and PROC SQL to Create Macro Arrays

Successful analysis in SAS often requires lengthy repetitive steps; the same code is executed on numerous data points. Iteration of SAS code is often easily achieved with the use of Macro Arrays. The programmer can employ numerous methods for creating these arrays, as well as choosing among different structural designs. PROC SQL and the SAS DATA step can each provide clear advantages to the creation of these arrays. PROC SQL and the DATA step can take advantage of descriptive information that is available to the programmer in the data set header line. This paper will discuss the benefits of the assignment of values to macro-variable array elements using CALL SYMPUTX() in the DATA step and the SELECT INTO expression in PROC SQL. This paper is intended for SAS programmers who have a working knowledge of Base SAS and the SAS Macro Facility. INTRODUCTION The elements of macro variables are text strings. These strings can consist of variable names, data set names, character strings, procedure or DATA step options, or any other list of items or code that can be processed by the SAS System. This paper will concentrate on the aspect of iteration and will be further narrowed to deal with the iteration of variable names, or variable descriptive information, as used by SAS procedures and DATA steps. Often an analysis will require iteration of SAS procedures for numerous data points. Executing a PROC MEANS or PROC FREQ for multiple variables can be as simple as listing the variables in the VAR or TABLES statement. However, if a programmer wishes to reduce the amount of output which must be reviewed or if the iteration of procedures is quite complex, it may be more prudent to process the analysis one data point(e.g. name of variable) at a time. One common method of achieving this type of iteration is with the use of Macro Arrays. If the list of data points is numbered in the tens, hundreds, or possibly even thousands, Macro Arrays can offer the programmer a method of reducing the amount of code that must be created. Macro Arrays are normally used in one of two constructs. 1) Array of Macro Variables: An ordinal list of macro variables where each contains one element to be processed. 2) Macro Variable Array: One macro variable containing the entire array of elements to be processed. The simplest way to create an Array of Macro Variables is by entering an ordinal sequence of macro variable assignments using %LET statements: %LET disease1=diabetes; %LET disease2=hbp; %LET disease3=cvd; %LET disease4=asthma; The Macro Variable Array can be assigned using one %LET statement: %LET disease=diabetes hbp cvd asthma; The SAS DATA step and PROC SQL can serve as excellent tools for creating these macro variable constructs. The method which a programmer chooses to use may vary, depending on programming preferences. ARRAYS Have you ever wished you could run code like the following? ARRAY symptom {*} symptom1-symptom40; ARRAY disease {*} diabetes--asthma; DO i = 1 TO DIM(symptom); DO j = 1 TO DIM(disease); PROC FREQ DATA=mydata; TABLES symptom{i}*disease{j} / CHISQ RELRISK; RUN; END; END; Applications Development SAS Global Forum 2008